﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

using CqusInfo.RAL.User;
using CqusInfo.CR.SqlHelper;

namespace CqusInfo.DAL.User
{
    public class User_InfoToSchoolDAL
    {
        public List<User_InfoToSchoolModel> Query_ByUserName(string userName)
        {
            List<User_InfoToSchoolModel> userList = new List<User_InfoToSchoolModel>();
            DataSet ds = SQLiteHelper.Query("tb_User_InfoToSchool", "c_vc_uitsId", userName, "c_b_isDel", false);
            string cacheId = string.Format("User_InfoToSchool_{0}", userName);
            userList = SqlData.GetQueryList<User_InfoToSchoolModel>(cacheId, "User_InfoToSchoolModel", ds);

            return userList;
        }

        /// <summary>
        /// 得到数据参数
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="model"></param>
        /// <returns>parameters</returns>
        private SQLiteParameter[] GetParameter(string userName, User_InfoToSchoolModel model)
        {
            SQLiteParameter[] parameters = { 
                                       new SQLiteParameter("@userName",DbType.String),
                                       new SQLiteParameter("@school",DbType.String),
                                       new SQLiteParameter("@schoolAddress",DbType.String),
                                       new SQLiteParameter("@departments",DbType.String),
                                       new SQLiteParameter("@professional",DbType.String),
                                       new SQLiteParameter("@class",DbType.String)
                                       };
            parameters[0].Value = userName;
            parameters[1].Value = model.c_vc_school;
            parameters[2].Value = model.c_vc_schoolAddress;
            parameters[3].Value = model.c_vc_departments;
            parameters[4].Value = model.c_vc_professional;
            parameters[5].Value = model.c_vc_class;

            return parameters;
        }

        public Int32 Add(string userName, User_InfoToSchoolModel model)
        {
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append("insert into tb_User_InfoToSchool values(");
            sqlStr.Append("c_vc_uitsId=@userName,c_vc_school=@school,c_vc_schoolAddress=@schoolAddress,c_vc_departments=@departments,c_vc_professional=@professional,c_vc_class=@class)");
            SQLiteParameter[] parameters = GetParameter(userName, model);

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), parameters);
        }

        public Int32 Update(string userName, User_InfoToSchoolModel model)
        {
            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append("update tb_User_InfoToSchool set ");
            sqlStr.Append("c_vc_school=@school,c_vc_schoolAddress=@schoolAddress,c_vc_departments=@departments,c_vc_professional=@professional,c_vc_class=@class where c_vc_uitsId=@userName)");
            SQLiteParameter[] parameters = GetParameter(userName, model);

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), parameters);
        }

        public Int32 Delete(string userName)
        {
            return SQLiteHelper.Delete("tb_User_InfoToSchool", "c_vc_uitsId", userName);
        }
    }
}
